/****** Object:  StoredProcedure [dbo].[factPurchaseCreditMemo]    Script Date: 28/04/2022 09:50:09 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[factPurchaseCreditMemo]') AND type in (N'P'))
DROP PROCEDURE [dbo].[factPurchaseCreditMemo]
GO

/****** Object:  StoredProcedure [dbo].[factPurchaseCreditMemo]    Script Date: 28/04/2022 09:50:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		Kristinn Ţór Sigurjónsson
-- Create date: 2022-04-20
-- Description:	SP for fact PurchaseCreditMemo
-- =============================================
CREATE PROCEDURE [dbo].[factPurchaseCreditMemo] 

	@CurrentRowID bigint,
	@NewRowID bigint
	
AS
BEGIN
SET NOCOUNT ON;

--CHECK if All needed Purchase staging tables exist
IF (Select count(*) from sys.tables where SCHEMA_NAME(schema_id) LIKE 'dbo' 
AND name IN ('stg$Purch_ Cr_ Memo Hdr_'
,'stg$Purch_ Cr_ Memo Line'
,'stg$Purch_ Inv_ Header'
,'stg$Purch_ Inv_ Line'
,'stg$Purch_ Rcpt_ Header'
,'stg$Purch_ Rcpt_ Line'
,'stg$Purchase Header'
,'stg$Purchase Header Archive'
,'stg$Purchase Line'
,'stg$Purchase Line Archive'))
= 10
AND COL_LENGTH('[dbo].[stg$Purchase Line]', '$systemCreatedAt') IS NOT NULL

WITH

	 /*
   Get Latest version of Headers
   */
	 tMaxPurchaseHeader
	 AS (SELECT [CompanyPrefix],
				CAST( [No_] AS VARCHAR(20) ) AS [No_],
				MAX( bigint_timestamp ) AS MaxTimestamp
		   FROM [dbo].[stg$Purchase Header]
		   GROUP BY [CompanyPrefix],
					[No_]),

	 tMaxPurchaseHeaderArchive
	 AS (SELECT [CompanyPrefix],
				CAST( [No_] AS VARCHAR(20) ) AS [No_],
				MAX( bigint_timestamp ) AS MaxTimestamp
		   FROM [dbo].[stg$Purchase Header Archive]
		   GROUP BY [CompanyPrefix],
					[No_]),

	 tMaxPurchaseLine
	 AS (SELECT CAST( [Document No_] AS VARCHAR(20) ) AS [NO_],
				[Line No_],
				[CompanyPrefix],
				MAX( bigint_timestamp ) AS maxbigint_timestamp
		   FROM [stg$Purchase Line]
		   GROUP BY [Document No_],
					[Line No_],
					[CompanyPrefix]),

	 tMaxPurchaseLineArchive
	 AS (SELECT CAST( [Document No_] AS VARCHAR(20) ) AS [NO_],
				[Line No_],
				[CompanyPrefix],
				MAX( bigint_timestamp ) AS maxbigint_timestamp
		   FROM [stg$Purchase Line Archive]
		   GROUP BY [Document No_],
					[Line No_],
					[CompanyPrefix]),


	 tMaxPurch_Cr_MemoLine
	 AS (SELECT CAST( [Order No_] AS VARCHAR(20) ) AS [NO_],
				[Line No_],
				[CompanyPrefix],
				MAX( bigint_timestamp ) AS maxbigint_timestamp
		   FROM [stg$Purch_ Cr_ Memo Line]
		   GROUP BY [Order No_],
					[Line No_],
					[CompanyPrefix]),

	 tMaxPurchaseCrMemoHeader
	 AS (SELECT [CompanyPrefix],
				CAST( [No_] AS VARCHAR(20) ) AS [Order No_],
				MAX( bigint_timestamp ) AS MaxTimestamp
		   FROM [dbo].[stg$Purch_ Cr_ Memo Hdr_]
		   GROUP BY [CompanyPrefix],
					[No_]),

	 tPurchaseHeader
	 AS (SELECT H.No_,
				H.[CompanyPrefix],
				H.[Posting Date],
				H.[Order Date],
				H.[Buy-from Vendor No_],
				H.[Status],
				H.[Expected Receipt Date],
				H.[Promised Receipt Date],
				H.[Requested Receipt Date]
		   FROM [stg$Purchase Header] H
		   LEFT JOIN
		   tMaxPurchaseHeader
		   ON tMaxPurchaseHeader.CompanyPrefix=H.CompanyPrefix
		   AND tMaxPurchaseHeader.No_=H.No_
		   AND MaxTimestamp=H.bigint_timestamp
		   GROUP BY H.No_,
					H.[CompanyPrefix],
					H.[Posting Date],
					H.[Order Date],
					H.[Buy-from Vendor No_],
					H.[Status],
					H.[Expected Receipt Date],
					H.[Promised Receipt Date],
					H.[Requested Receipt Date]),

	 tPurchaseHeaderArchive
	 AS (SELECT H.No_,
				H.[CompanyPrefix],
				H.[Posting Date],
				H.[Order Date],
				H.[Buy-from Vendor No_],
				H.[Status],
				H.[Expected Receipt Date],
				H.[Promised Receipt Date],
				H.[Requested Receipt Date]
		   FROM [stg$Purchase Header Archive] H
		   LEFT JOIN
		   tMaxPurchaseHeaderArchive MaxHA
		   ON MaxHA.CompanyPrefix=H.CompanyPrefix
		   AND MaxHA.No_=H.No_
		   AND MaxHA.MaxTimestamp=H.bigint_timestamp
		   WHERE H.[Version No_]=1
		   GROUP BY H.No_,
					H.[CompanyPrefix],
					H.[Posting Date],
					H.[Order Date],
					H.[Buy-from Vendor No_],
					H.[Status],
					H.[Expected Receipt Date],
					H.[Promised Receipt Date],
					H.[Requested Receipt Date]),

	 tPurchaseCrMemoHeader
	 AS (SELECT PCH.[CompanyPrefix],
				PCH.[Order No_],
				PCH.[Order Date],
				PCH.[Posting Date],
				PCH.[Buy-from Vendor No_]
		   FROM [stg$Purch_ Rcpt_ Header] PCH
		   LEFT JOIN
		   tMaxPurchaseHeader
		   ON tMaxPurchaseHeader.CompanyPrefix=PCH.CompanyPrefix
		   AND tMaxPurchaseHeader.No_=PCH.No_
		   AND MaxTimestamp=PCH.bigint_timestamp
		   GROUP BY PCH.[CompanyPrefix],
					PCH.[Order No_],
					PCH.[Order Date],
					PCH.[Posting Date],
					PCH.[Buy-from Vendor No_]),

	 tPurchaseLine
	 AS (SELECT sPL.[CompanyPrefix],
				sPL.[Document No_],
				sPL.[Line No_],
				sPL.[Promised Receipt Date],
				sPL.[Planned Receipt Date],
				sPL.[Expected Receipt Date]
		   FROM [stg$Purchase Line] sPL
		   LEFT JOIN
		   tMaxPurchaseLine
		   ON tMaxPurchaseLine.CompanyPrefix=sPL.CompanyPrefix
		   AND tMaxPurchaseLine.No_=sPL.[Document No_]
		   AND maxbigint_timestamp=sPL.bigint_timestamp
		   AND tMaxPurchaseLine.[Line No_]=sPL.[Line No_]
		   GROUP BY sPL.[CompanyPrefix],
					sPL.[Document No_],
					sPL.[Line No_],
					sPL.[Promised Receipt Date],
					sPL.[Planned Receipt Date],
					sPL.[Expected Receipt Date]),

	 tPurchaseLineArchive
	 AS (SELECT PLA.[CompanyPrefix],
				PLA.[Document No_],
				PLA.[Line No_],
				PLA.[Promised Receipt Date],
				PLA.[Planned Receipt Date],
				PLA.[Expected Receipt Date]
		   FROM [stg$Purchase Line Archive] PLA
		   LEFT JOIN
		   tMaxPurchaseLineArchive
		   ON tMaxPurchaseLineArchive.CompanyPrefix=PLA.CompanyPrefix
		   AND tMaxPurchaseLineArchive.No_=PLA.[Document No_]
		   AND tMaxPurchaseLineArchive.maxbigint_timestamp=PLA.bigint_timestamp
		   AND tMaxPurchaseLineArchive.[Line No_]=PLA.[Line No_]
		   WHERE PLA.[Version No_]=1
		   GROUP BY PLA.[CompanyPrefix],
					PLA.[Document No_],
					PLA.[Line No_],
					PLA.[Promised Receipt Date],
					PLA.[Planned Receipt Date],
					PLA.[Expected Receipt Date]),

	 tPurch_Cr_MemoLine
	 AS (SELECT PCML.*
		   FROM [stg$Purch_ Cr_ Memo Line] PCML
		   LEFT JOIN
		   tMaxPurch_Cr_MemoLine
		   ON tMaxPurch_Cr_MemoLine.CompanyPrefix=PCML.CompanyPrefix
		   AND tMaxPurch_Cr_MemoLine.No_=PCML.[Document No_]
		   AND tMaxPurch_Cr_MemoLine.maxbigint_timestamp=PCML.bigint_timestamp
		   AND tMaxPurch_Cr_MemoLine.[Line No_]=PCML.[Line No_]
		   
		   WHERE PCML.[bigint_timestamp] BETWEEN @CurrentRowID AND @NewRowID
		   ),


	 tDistinctDocumentLineNumbers
	 AS (SELECT CAST( [No_] AS VARCHAR(20) ) AS [No_],
				[Line No_],
				[CompanyPrefix]
		   FROM tMaxPurch_Cr_MemoLine
		   WHERE [No_]<>''
		   GROUP BY [No_],
					[Line No_],
					[CompanyPrefix]),

	 /*
   Get the list of companies used
   */
	 tCompanies
	 AS (SELECT dCOM.[SK_Company] AS [Company],
				dCOM.[CompanyPrefix]
		   FROM [DW].[dCompany] dCOM
		   WHERE dCOM.[SK_Company]<>-1),
	 /*
   Set up dimensions and support tables used with the main data process
   */
	 tLocation
	 AS (SELECT dLOC.[SK_Location],
				tCOM.[CompanyPrefix],
				dLOC.[LocationCode]
		   FROM DW.[dLocation] dLOC
		   LEFT OUTER JOIN
		   [tCompanies] tCOM
		   ON dLOC.[Company]=tCOM.[Company]),

	 tItem
	 AS (SELECT dITM.[SK_Item],
				tCOM.[CompanyPrefix],
				dITM.[No],
				dITM.ValidFrom,
				dITM.ValidTo
		   FROM [DW].[dItem] dITM
		   LEFT OUTER JOIN
		   tCompanies tCOM
		   ON dITM.[Company]=tCOM.[Company]),
	 tStore
	 AS (SELECT dSTO.[SK_Store],
				dSTO.[StoreNo],
				tCOM.[CompanyPrefix]
		   FROM [DW].[dStore] dSTO
		   LEFT OUTER JOIN
		   [tCompanies] tCOM
		   ON dSTO.[Company]=tCOM.[Company]),
	 tVendor
	 AS (SELECT dVEN.SK_Vendor,
				dVEN.VendorNo,
				tCOM.CompanyPrefix
		   FROM DW.dVendor dVEN
		   LEFT OUTER JOIN
		   [tCompanies] tCOM
		   ON dVEN.[Company]=tCOM.[Company]),

	 tReasons
	 AS (SELECT dRea.[SK_ReturnReason],
				dRea.[Code],
				tCOM.CompanyPrefix
		   FROM [DW].[dReturnReason] dRea
		   LEFT OUTER JOIN
		   [tCompanies] tCOM
		   ON dRea.[Company]=tCOM.[Company]),

	 tPurchases
	 AS (SELECT DistNo.[CompanyPrefix],
				PCMH.[Buy-from Vendor No_] AS [Buy-from Vendor No_],
				CAST( [DistNo].[No_] AS VARCHAR(20) ) AS [Order Number],
				PCML.[Order Line No_] AS [Order Line No],
				PCML.[Location Code] AS [Location Code],
				PCML.[No_] AS [Item Number],
				PCML.[Posting Date] AS [Credit Memo Posting Date],
				PCMH.[Order Date],
				CAST( PCML.[$systemCreatedAt] AS DATETIME ) AS [Purchase Credit Memo Create Date],
				COALESCE( PH.[Posting Date], PHA.[Posting Date], PCMH.[Posting Date] ) AS [Posting Date],
				COALESCE( sPL.[Promised Receipt Date], PLA.[Promised Receipt Date] ) AS [Promised Receipt Date],
				PCML.[Quantity] AS [Quantity Credited],
				PCML.[Return Reason Code] AS [Return Reason Code],
				COALESCE( PH.[Status], PHA.[Status] ) AS [Status]
		   FROM tDistinctDocumentLineNumbers DistNo
		   LEFT JOIN
		   tPurchaseLine sPL
		   ON [DistNo].[No_]=sPL.[Document No_]
		   AND DistNo.[CompanyPrefix]=sPL.[CompanyPrefix]
		   AND DistNo.[Line No_]=sPL.[Line No_]
		   LEFT JOIN
		   tPurchaseHeader PH
		   ON [DistNo].[No_]=PH.[No_]
		   AND DistNo.[CompanyPrefix]=PH.[CompanyPrefix]
		   LEFT OUTER JOIN
		   tPurchaseCrMemoHeader PCMH
		   ON [DistNo].[No_]=PCMH.[Order No_]
		   AND DistNo.[CompanyPrefix]=PCMH.[CompanyPrefix]
		   LEFT OUTER JOIN
		   tPurch_Cr_MemoLine PCML
		   ON [DistNo].[No_]=PCML.[Order No_]
		   AND DistNo.[CompanyPrefix]=PCML.[CompanyPrefix]
		   AND DistNo.[Line No_]=PCML.[Line No_]
		   LEFT JOIN
		   tPurchaseLineArchive PLA
		   ON [DistNo].[No_]=PLA.[Document No_]
		   AND DistNo.[CompanyPrefix]=PLA.[CompanyPrefix]
		   AND DistNo.[Line No_]=PLA.[Line No_]
		   LEFT JOIN
		   tPurchaseHeaderArchive PHA
		   ON [DistNo].[No_]=PHA.[No_]
		   AND DistNo.[CompanyPrefix]=PHA.[CompanyPrefix])


MERGE [DW].[fPurchaseCreditMemo] AS TARGET USING (SELECT tPL.[CompanyPrefix],
	 cast(tPL.[Order Number] as varchar(50)) + '-' + cast(tPL.[Order Line No] as varchar(10))  + '-' + cast(tPL.[CompanyPrefix] as varchar(50)) AS OrderNumberAndLineKey,
				COALESCE( tVen.[SK_Vendor],-1 ) AS [SK_Vendor],
				COALESCE( tITM.[SK_Item],-1 ) AS [SK_Item],
				COALESCE( tLOC.[SK_Location],-1 ) AS [SK_Location],
				COALESCE( tRe.[SK_ReturnReason],-1 ) AS [SK_ReturnReason],
				IIF( tPL.[Credit Memo Posting Date]='1753-01-01', NULL, tPL.[Credit Memo Posting Date] ) AS [Credit Memo Posting Date],
				IIF( tPL.[Purchase Credit Memo Create Date]='1753-01-01', NULL, tPL.[Purchase Credit Memo Create Date] ) AS [Purchase Credit Memo Create Date],
				IIF( tPL.[Posting Date]='1753-01-01', NULL, tPL.[Posting Date] ) AS [Posting Date],
				tPL.[Order Number] AS [Order Number],
				tPL.[Order Line No] AS [Order Line No],
				tPL.[Quantity Credited] AS [Quantity Credited_],
				tPL.[Status] AS [Status],
				GETDATE( ) AS 'Batchdate'
		   FROM [tPurchases] tPL
		   LEFT OUTER JOIN
		   [tItem] tITM
		   ON tPL.[CompanyPrefix]=tITM.[CompanyPrefix]
		   AND tPL.[Item Number]=tITM.[No]
		   AND tPL.[Posting Date]>=tITM.ValidFrom
		   AND tPL.[Posting Date]<=tITM.ValidTo -- GETTING THE CORRECT SURROGATE KEY VERSION OF THE DIMENSION (SCD Type2)	
		   LEFT OUTER JOIN
		   [tLocation] tLOC
		   ON tPL.[CompanyPrefix]=tLOC.[CompanyPrefix]
		   AND tPL.[Location Code]=tLOC.[LocationCode]
		   LEFT OUTER JOIN
		   tVendor tVen
		   ON tPL.CompanyPrefix=tVen.CompanyPrefix
		   AND tPL.[Buy-from Vendor No_]=tVen.VendorNo
		   LEFT OUTER JOIN
		   [tReasons] tRe
		   ON [tPL].[CompanyPrefix]=tRe.[CompanyPrefix]
		   AND [tPL].[Return Reason Code]=tRe.[Code]

	) AS Source
ON Target.OrderNumberAndLineKey = Source.OrderNumberAndLineKey
	
WHEN MATCHED
	THEN UPDATE
		SET [CompanyPrefix]	=	Source.[CompanyPrefix]
,[SK_Vendor]	=	Source.[SK_Vendor]
,[SK_Item]	=	Source.[SK_Item]
,[SK_Location]	=	Source.[SK_Location]
,[SK_ReturnReason]	=	Source.[SK_ReturnReason]
,[Credit Memo Posting Date]	=	Source.[Credit Memo Posting Date]
,[Purchase Credit Memo Create Date]	=	Source.[Purchase Credit Memo Create Date]
,[Posting Date]	=	Source.[Posting Date]
,[Order Number]	=	Source.[Order Number]
,[Order Line No]	=	Source.[Order Line No]
,[Quantity Credited_]	=	Source.[Quantity Credited_]
,[Status]	=	Source.[Status]
,[Batchdate]	=	Source.[Batchdate]

WHEN NOT MATCHED BY TARGET
	THEN INSERT ([CompanyPrefix]
      ,[OrderNumberAndLineKey]
      ,[SK_Vendor]
      ,[SK_Item]
      ,[SK_Location]
      ,[SK_ReturnReason]
      ,[Credit Memo Posting Date]
      ,[Purchase Credit Memo Create Date]
      ,[Posting Date]
      ,[Order Number]
      ,[Order Line No]
      ,[Quantity Credited_]
      ,[Status]
      ,[Batchdate])
			VALUES (Source.[CompanyPrefix]
      ,Source.[OrderNumberAndLineKey]
      ,Source.[SK_Vendor]
      ,Source.[SK_Item]
      ,Source.[SK_Location]
      ,Source.[SK_ReturnReason]
      ,Source.[Credit Memo Posting Date]
      ,Source.[Purchase Credit Memo Create Date]
      ,Source.[Posting Date]
      ,Source.[Order Number]
      ,Source.[Order Line No]
      ,Source.[Quantity Credited_]
      ,Source.[Status]
      ,Source.[Batchdate])
;
ELSE
SELECT 'RowCount' = 0
END

SELECT
	'RowCount' = @@rowcount
GO


/****** Object:  StoredProcedure [dbo].[factPurchaseInvoice]    Script Date: 28/04/2022 09:54:04 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[factPurchaseInvoice]') AND type in (N'P'))
DROP PROCEDURE [dbo].[factPurchaseInvoice]
GO

/****** Object:  StoredProcedure [dbo].[factPurchaseInvoice]    Script Date: 28/04/2022 09:54:04 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		Kristinn Ţór Sigurjónsson
-- Create date: 2022-04-27
-- Description:	SP for factPurchaseInvoice
-- =============================================
CREATE PROCEDURE [dbo].[factPurchaseInvoice] 

	@CurrentRowID bigint,
	@NewRowID bigint
	
AS
BEGIN
SET NOCOUNT ON;

--CHECK if All needed Purchase staging tables exist
IF (Select count(*) from sys.tables where SCHEMA_NAME(schema_id) LIKE 'dbo' 
AND name IN ('stg$Purch_ Cr_ Memo Hdr_'
,'stg$Purch_ Cr_ Memo Line'
,'stg$Purch_ Inv_ Header'
,'stg$Purch_ Inv_ Line'
,'stg$Purch_ Rcpt_ Header'
,'stg$Purch_ Rcpt_ Line'
,'stg$Purchase Header'
,'stg$Purchase Header Archive'
,'stg$Purchase Line'
,'stg$Purchase Line Archive'))
= 10
AND COL_LENGTH('[dbo].[stg$Purchase Line]', '$systemCreatedAt') IS NOT NULL

WITH

	 /*
   Get Latest version of Headers
   */
	 tMaxPurchaseHeader
	 AS (SELECT [CompanyPrefix],
				CAST( [No_] AS VARCHAR(20) ) AS [No_],
				MAX( bigint_timestamp ) AS MaxTimestamp
		   FROM [dbo].[stg$Purchase Header]
		   GROUP BY [CompanyPrefix],
					[No_]),

	 tMaxPurchaseHeaderArchive
	 AS (SELECT [CompanyPrefix],
				CAST( [No_] AS VARCHAR(20) ) AS [No_],
				MAX( bigint_timestamp ) AS MaxTimestamp
		   FROM [dbo].[stg$Purchase Header Archive]
		   GROUP BY [CompanyPrefix],
					[No_]),

	 tMaxPurchaseLine
	 AS (SELECT CAST( [Document No_] AS VARCHAR(20) ) AS [NO_],
				[Line No_],
				[CompanyPrefix],
				MAX( bigint_timestamp ) AS maxbigint_timestamp
		   FROM [stg$Purchase Line]
		   GROUP BY [Document No_],
					[Line No_],
					[CompanyPrefix]),

	 tMaxPurchaseLineArchive
	 AS (SELECT CAST( [Document No_] AS VARCHAR(20) ) AS [NO_],
				[Line No_],
				[CompanyPrefix],
				MAX( bigint_timestamp ) AS maxbigint_timestamp
		   FROM [stg$Purchase Line Archive]
		   GROUP BY [Document No_],
					[Line No_],
					[CompanyPrefix]),

	 tMaxPurch_Inv_Line
	 AS (SELECT CAST( [Order No_] AS VARCHAR(20) ) AS [NO_],
				[Order Line No_] AS [Line No_],
				[CompanyPrefix],
				MAX( bigint_timestamp ) AS maxbigint_timestamp
		   FROM [stg$Purch_ Inv_ Line]
		   GROUP BY [Order No_],
					[Order Line No_],
					[CompanyPrefix]),

	 tMaxPurchaseInvHeader
	 AS (SELECT [CompanyPrefix],
				CAST( [No_] AS VARCHAR(20) ) AS [Order No_],
				MAX( bigint_timestamp ) AS MaxTimestamp
		   FROM [dbo].[stg$Purch_ Inv_ Header]
		   GROUP BY [CompanyPrefix],
					[No_]),

	 tPurchaseHeader
	 AS (SELECT H.No_,
				H.[CompanyPrefix],
				H.[Posting Date],
				H.[Order Date],
				H.[Buy-from Vendor No_],
				H.[Status],
				H.[Expected Receipt Date],
				H.[Promised Receipt Date],
				H.[Requested Receipt Date]
		   FROM [stg$Purchase Header] H
		   LEFT JOIN
		   tMaxPurchaseHeader
		   ON tMaxPurchaseHeader.CompanyPrefix=H.CompanyPrefix
		   AND tMaxPurchaseHeader.No_=H.No_
		   AND MaxTimestamp=H.bigint_timestamp
		   GROUP BY H.No_,
					H.[CompanyPrefix],
					H.[Posting Date],
					H.[Order Date],
					H.[Buy-from Vendor No_],
					H.[Status],
					H.[Expected Receipt Date],
					H.[Promised Receipt Date],
					H.[Requested Receipt Date]),

	 tPurchaseHeaderArchive
	 AS (SELECT H.No_,
				H.[CompanyPrefix],
				H.[Posting Date],
				H.[Order Date],
				H.[Buy-from Vendor No_],
				H.[Status],
				H.[Expected Receipt Date],
				H.[Promised Receipt Date],
				H.[Requested Receipt Date]
		   FROM [stg$Purchase Header Archive] H
		   LEFT JOIN
		   tMaxPurchaseHeaderArchive MaxHA
		   ON MaxHA.CompanyPrefix=H.CompanyPrefix
		   AND MaxHA.No_=H.No_
		   AND MaxHA.MaxTimestamp=H.bigint_timestamp
		   WHERE H.[Version No_]=1
		   GROUP BY H.No_,
					H.[CompanyPrefix],
					H.[Posting Date],
					H.[Order Date],
					H.[Buy-from Vendor No_],
					H.[Status],
					H.[Expected Receipt Date],
					H.[Promised Receipt Date],
					H.[Requested Receipt Date]),

	 tPurchaseInvHeader
	 AS (SELECT H.[Order No_],
				H.[CompanyPrefix],
				H.[Posting Date],
				H.[Order Date],
				H.[Buy-from Vendor No_]
		   FROM [stg$Purch_ Inv_ Header] H
		   LEFT JOIN
		   tMaxPurchaseInvHeader
		   ON tMaxPurchaseInvHeader.CompanyPrefix=H.CompanyPrefix
		   AND tMaxPurchaseInvHeader.[Order No_]=H.No_
		   AND tMaxPurchaseInvHeader.MaxTimestamp=H.bigint_timestamp
		   GROUP BY H.[Order No_],
					H.[CompanyPrefix],
					H.[Posting Date],
					H.[Order Date],
					H.[Buy-from Vendor No_]),

	 tPurch_Inv_Line
	 AS (SELECT PIL.*
		   FROM [stg$Purch_ Inv_ Line] PIL
		   LEFT JOIN
		   tMaxPurch_Inv_Line
		   ON tMaxPurch_Inv_Line.CompanyPrefix=PIL.CompanyPrefix
		   AND tMaxPurch_Inv_Line.No_=PIL.[Document No_]
		   AND tMaxPurch_Inv_Line.maxbigint_timestamp=PIL.bigint_timestamp
		   AND tMaxPurch_Inv_Line.[Line No_]=PIL.[Order Line No_]),


	 tPurchaseLine
	 AS (SELECT sPL.[CompanyPrefix],
				sPL.[Document No_],
				sPL.[Line No_],
				sPL.[Promised Receipt Date],
				sPL.[Planned Receipt Date],
				sPL.[Expected Receipt Date]
		   FROM [stg$Purchase Line] sPL
		   LEFT JOIN
		   tMaxPurchaseLine
		   ON tMaxPurchaseLine.CompanyPrefix=sPL.CompanyPrefix
		   AND tMaxPurchaseLine.No_=sPL.[Document No_]
		   AND maxbigint_timestamp=sPL.bigint_timestamp
		   AND tMaxPurchaseLine.[Line No_]=sPL.[Line No_]
		   GROUP BY sPL.[CompanyPrefix],
					sPL.[Document No_],
					sPL.[Line No_],
					sPL.[Promised Receipt Date],
					sPL.[Planned Receipt Date],
					sPL.[Expected Receipt Date]),

	 tPurchaseLineArchive
	 AS (SELECT PLA.[CompanyPrefix],
				PLA.[Document No_],
				PLA.[Line No_],
				PLA.[Promised Receipt Date],
				PLA.[Planned Receipt Date],
				PLA.[Expected Receipt Date]
		   FROM [stg$Purchase Line Archive] PLA
		   LEFT JOIN
		   tMaxPurchaseLineArchive
		   ON tMaxPurchaseLineArchive.CompanyPrefix=PLA.CompanyPrefix
		   AND tMaxPurchaseLineArchive.No_=PLA.[Document No_]
		   AND tMaxPurchaseLineArchive.maxbigint_timestamp=PLA.bigint_timestamp
		   AND tMaxPurchaseLineArchive.[Line No_]=PLA.[Line No_]
		   WHERE PLA.[Version No_]=1
		   GROUP BY PLA.[CompanyPrefix],
					PLA.[Document No_],
					PLA.[Line No_],
					PLA.[Promised Receipt Date],
					PLA.[Planned Receipt Date],
					PLA.[Expected Receipt Date]),


	 tDistinctDocumentLineNumbers
	 AS (SELECT CAST( [No_] AS VARCHAR(20) ) AS [No_],
				[Line No_],
				[CompanyPrefix]
		   FROM tMaxPurch_Inv_Line
		   WHERE [No_]<>''
		   GROUP BY [No_],
					[Line No_],
					[CompanyPrefix]),

	 /*
   Get the list of companies used
   */
	 tCompanies
	 AS (SELECT dCOM.[SK_Company] AS [Company],
				dCOM.[CompanyPrefix]
		   FROM [DW].[dCompany] dCOM
		   WHERE dCOM.[SK_Company]<>-1),
	 /*
   Set up dimensions and support tables used with the main data process
   */
	 tLocation
	 AS (SELECT dLOC.[SK_Location],
				tCOM.[CompanyPrefix],
				dLOC.[LocationCode]
		   FROM DW.[dLocation] dLOC
		   LEFT OUTER JOIN
		   [tCompanies] tCOM
		   ON dLOC.[Company]=tCOM.[Company]),

	 tItem
	 AS (SELECT dITM.[SK_Item],
				tCOM.[CompanyPrefix],
				dITM.[No],
				dITM.ValidFrom,
				dITM.ValidTo
		   FROM [DW].[dItem] dITM
		   LEFT OUTER JOIN
		   tCompanies tCOM
		   ON dITM.[Company]=tCOM.[Company]),
	 tStore
	 AS (SELECT dSTO.[SK_Store],
				dSTO.[StoreNo],
				tCOM.[CompanyPrefix]
		   FROM [DW].[dStore] dSTO
		   LEFT OUTER JOIN
		   [tCompanies] tCOM
		   ON dSTO.[Company]=tCOM.[Company]),
	 tVendor
	 AS (SELECT dVEN.SK_Vendor,
				dVEN.VendorNo,
				tCOM.CompanyPrefix
		   FROM DW.dVendor dVEN
		   LEFT OUTER JOIN
		   [tCompanies] tCOM
		   ON dVEN.[Company]=tCOM.[Company]),

	 tPurchases
	 AS (SELECT DistNo.[CompanyPrefix],
				PIH.[Buy-from Vendor No_] AS [Buy-from Vendor No_],
				CAST( [DistNo].[No_] AS VARCHAR(20) ) AS [Order Number],
				PIL.[Line No_] AS [Order Line No],
				PIL.[Location Code] AS [Location Code],
				PIL.[No_] AS [Item Number],
				PIL.[Posting Date] AS [Invoice Posting Date],
				CAST( PIL.[$systemCreatedAt] AS DATETIME ) AS [Purchase_Invoice_Create_Date],
				COALESCE( PH.[Posting Date], PHA.[Posting Date], PIH.[Posting Date] ) AS [Posting Date],
				PIL.[Quantity] AS [Quantity Invoiced],
				COALESCE( PH.[Status], PHA.[Status] ) AS [Status]
		   FROM tDistinctDocumentLineNumbers DistNo
		   LEFT JOIN
		   tPurchaseLine sPL
		   ON [DistNo].[No_]=sPL.[Document No_]
		   AND DistNo.[CompanyPrefix]=sPL.[CompanyPrefix]
		   AND DistNo.[Line No_]=sPL.[Line No_]
		   LEFT JOIN
		   tPurchaseHeader PH
		   ON [DistNo].[No_]=PH.[No_]
		   AND DistNo.[CompanyPrefix]=PH.[CompanyPrefix]
		   LEFT OUTER JOIN
		   tPurchaseInvHeader PIH
		   ON [DistNo].[No_]=PIH.[Order No_]
		   AND DistNo.[CompanyPrefix]=PIH.[CompanyPrefix]
		   LEFT OUTER JOIN
		   tPurch_Inv_Line PIL
		   ON [DistNo].[No_]=PIL.[Order No_]
		   AND DistNo.[CompanyPrefix]=PIL.[CompanyPrefix]
		   AND DistNo.[Line No_]=PIL.[Order Line No_]
		   LEFT JOIN
		   tPurchaseLineArchive PLA
		   ON [DistNo].[No_]=PLA.[Document No_]
		   AND DistNo.[CompanyPrefix]=PLA.[CompanyPrefix]
		   AND DistNo.[Line No_]=PLA.[Line No_]
		   LEFT JOIN
		   tPurchaseHeaderArchive PHA
		   ON [DistNo].[No_]=PHA.[No_]
		   AND DistNo.[CompanyPrefix]=PHA.[CompanyPrefix])

MERGE [DW].[fPurchaseInvoice] AS TARGET USING (SELECT tPL.[CompanyPrefix],
	 cast(tPL.[Order Number] as varchar(50)) + '-' + cast(tPL.[Order Line No] as varchar(10))  + '-' + cast(tPL.[CompanyPrefix] as varchar(50)) AS OrderNumberAndLineKey,
				COALESCE( tVen.[SK_Vendor],-1 ) AS [SK_Vendor],
				COALESCE( tITM.[SK_Item],-1 ) AS [SK_Item],
				COALESCE( tLOC.[SK_Location],-1 ) AS [SK_Location],
				IIF( tPL.[Invoice Posting Date]='1753-01-01', NULL, tPL.[Invoice Posting Date] ) AS [Invoice Posting Date],
				IIF( tPL.[Purchase_Invoice_Create_Date]='1753-01-01', NULL, tPL.[Purchase_Invoice_Create_Date] ) AS [Purchase Invoice Create Date],
				tPL.[Order Number] AS [Order Number],
				tPL.[Order Line No] AS [Order Line No],
				tPL.[Quantity Invoiced] AS [Quantity Invoiced_],
				tPL.[Status] AS [Status],
				GETDATE( ) AS 'Batchdate'
		   FROM [tPurchases] tPL
		   LEFT OUTER JOIN
		   [tItem] tITM
		   ON tPL.[CompanyPrefix]=tITM.[CompanyPrefix]
		   AND tPL.[Item Number]=tITM.[No]
		   AND tPL.[Posting Date]>=tITM.ValidFrom
		   AND tPL.[Posting Date]<=tITM.ValidTo -- GETTING THE CORRECT SURROGATE KEY VERSION OF THE DIMENSION (SCD Type2)	
		   LEFT OUTER JOIN
		   [tLocation] tLOC
		   ON tPL.[CompanyPrefix]=tLOC.[CompanyPrefix]
		   AND tPL.[Location Code]=tLOC.[LocationCode]
		   LEFT OUTER JOIN
		   tVendor tVen
		   ON tPL.CompanyPrefix=tVen.CompanyPrefix
		   AND tPL.[Buy-from Vendor No_]=tVen.VendorNo

	) AS Source
ON Target.OrderNumberAndLineKey = Source.OrderNumberAndLineKey
	
WHEN MATCHED
	THEN UPDATE
		SET [CompanyPrefix] = Source.[CompanyPrefix]
,[OrderNumberAndLineKey] = Source.[OrderNumberAndLineKey]
,[SK_Vendor] = Source.[SK_Vendor]
,[SK_Item] = Source.[SK_Item]
,[SK_Location] = Source.[SK_Location]
,[Invoice Posting Date] = Source.[Invoice Posting Date]
,[Purchase Invoice Create Date] = Source.[Purchase Invoice Create Date]
,[Order Number] = Source.[Order Number]
,[Order Line No] = Source.[Order Line No]
,[Quantity Invoiced_] = Source.[Quantity Invoiced_]
,[Status] = Source.[Status]
,[Batchdate] = Source.[Batchdate]


WHEN NOT MATCHED BY TARGET
	THEN INSERT ([CompanyPrefix]
      ,[OrderNumberAndLineKey]
      ,[SK_Vendor]
      ,[SK_Item]
      ,[SK_Location]
      ,[Invoice Posting Date]
      ,[Purchase Invoice Create Date]
      ,[Order Number]
      ,[Order Line No]
      ,[Quantity Invoiced_]
      ,[Status]
      ,[Batchdate])
			VALUES (Source.[CompanyPrefix]
,Source.[OrderNumberAndLineKey]
,Source.[SK_Vendor]
,Source.[SK_Item]
,Source.[SK_Location]
,Source.[Invoice Posting Date]
,Source.[Purchase Invoice Create Date]
,Source.[Order Number]
,Source.[Order Line No]
,Source.[Quantity Invoiced_]
,Source.[Status]
,Source.[Batchdate]
)
;
ELSE
SELECT 'RowCount' = 0
END

SELECT
	'RowCount' = @@rowcount
GO


/****** Object:  StoredProcedure [dbo].[factPurchaseOrders]    Script Date: 28/04/2022 09:54:24 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[factPurchaseOrders]') AND type in (N'P'))
DROP PROCEDURE [dbo].[factPurchaseOrders]
GO

/****** Object:  StoredProcedure [dbo].[factPurchaseOrders]    Script Date: 28/04/2022 09:54:24 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		Kristinn Ţór Sigurjónsson
-- Create date: 2022-04-27
-- Description:	SP for factPurchaseOrders
-- =============================================
CREATE PROCEDURE [dbo].[factPurchaseOrders] 

	@CurrentRowID bigint,
	@NewRowID bigint
	
AS
BEGIN
SET NOCOUNT ON;

--CHECK if All needed Purchase staging tables exist
IF (Select count(*) from sys.tables where SCHEMA_NAME(schema_id) LIKE 'dbo' 
AND name IN ('stg$Purch_ Cr_ Memo Hdr_'
,'stg$Purch_ Cr_ Memo Line'
,'stg$Purch_ Inv_ Header'
,'stg$Purch_ Inv_ Line'
,'stg$Purch_ Rcpt_ Header'
,'stg$Purch_ Rcpt_ Line'
,'stg$Purchase Header'
,'stg$Purchase Header Archive'
,'stg$Purchase Line'
,'stg$Purchase Line Archive'))
= 10
AND COL_LENGTH('[dbo].[stg$Purchase Line]', '$systemCreatedAt') IS NOT NULL

WITH

/*
Get Latest version of Headers
*/
tMaxPurchaseHeader
AS

(SELECT
		[CompanyPrefix]
	   ,CAST([No_] AS VARCHAR(20)) AS [No_]
	   ,MAX(bigint_timestamp) AS MaxTimestamp
	FROM [dbo].[stg$Purchase Header]
	GROUP BY [CompanyPrefix]
			,[No_]
			),

tMaxPurchaseHeaderArchive
AS

(SELECT
		[CompanyPrefix]
	   ,CAST([No_] AS VARCHAR(20)) AS [No_]
	   ,MAX(bigint_timestamp) AS MaxTimestamp
	FROM [dbo].[stg$Purchase Header Archive]
	GROUP BY [CompanyPrefix]
			,[No_]
			),
tMaxPurchaseLine
AS
(SELECT
		CAST([Document No_] AS VARCHAR(20)) AS [NO_]
	   ,[Line No_]
	   ,[CompanyPrefix]
	   ,MAX(bigint_timestamp) AS maxbigint_timestamp
	FROM [stg$Purchase Line]
	GROUP BY [Document No_]
			,[Line No_]
			,[CompanyPrefix]),

tMaxPurchaseLineArchive AS (
	SELECT
		CAST([Document No_] AS VARCHAR(20)) AS [NO_]
	   ,[Line No_]
	   ,[CompanyPrefix]
	   ,MAX(bigint_timestamp) AS maxbigint_timestamp
	FROM [stg$Purchase Line Archive]
	GROUP BY [Document No_]
			,[Line No_]
			,[CompanyPrefix]),

tMaxPurch_Rcpt_Line AS (
	SELECT
		CAST([Order No_] AS VARCHAR(20)) AS [NO_]
	   ,[Line No_]
	   ,[CompanyPrefix]
	   ,MAX(bigint_timestamp) AS maxbigint_timestamp
	FROM [stg$Purch_ Rcpt_ Line]
	GROUP BY [Order No_]
			,[Line No_]
			,[CompanyPrefix]),

tPurchaseHeader AS (Select H.* from [stg$Purchase Header] H
LEFT JOIN tMaxPurchaseHeader
ON  tMaxPurchaseHeader.CompanyPrefix = H.CompanyPrefix
AND tMaxPurchaseHeader.No_ = H.No_
AND MaxTimestamp = H.bigint_timestamp),

tPurchaseHeaderArchive AS (Select H.* from [stg$Purchase Header Archive] H
LEFT JOIN tMaxPurchaseHeader
ON  tMaxPurchaseHeader.CompanyPrefix = H.CompanyPrefix
AND tMaxPurchaseHeader.No_ = H.No_
AND MaxTimestamp = H.bigint_timestamp
WHERE H.[Version No_] = 1),

tPurchaseLine as (Select sPL.* from [stg$Purchase Line] sPL 
	left join tMaxPurchaseLine 
on 	tMaxPurchaseLine.CompanyPrefix	=	 sPL.CompanyPrefix
AND 	tMaxPurchaseLine.NO_	=	 sPL.[Document No_] 
AND 	maxbigint_timestamp	=	 sPL.bigint_timestamp 
AND 	tMaxPurchaseLine.[Line No_]	=	 sPL.[Line No_] 

		),
tPurchaseLineArchive as (Select PLA.* from [stg$Purchase Line Archive] PLA 
	left join  tMaxPurchaseLineArchive 
on 	tMaxPurchaseLineArchive.CompanyPrefix	=	 PLA.CompanyPrefix
AND 	tMaxPurchaseLineArchive.NO_	=	 PLA.[Document No_] 
AND 	tMaxPurchaseLineArchive.maxbigint_timestamp	=	 PLA.bigint_timestamp 
AND 	tMaxPurchaseLineArchive.[Line No_]	=	 PLA.[Line No_] 
WHERE PLA.[Version No_]= 1

		),

tSUMPurch_Rcpt_Line as (Select PRL.CompanyPrefix, PRL.[Order No_], PRL.[Order Line No_], MAX(PRL.[$systemCreatedAt]) AS MaxRcptDate ,SUM(PRL.Quantity) AS SumRcptQuantity from [stg$Purch_ Rcpt_ Line] PRL 
	left join  tMaxPurch_Rcpt_Line 
on 	tMaxPurch_Rcpt_Line.CompanyPrefix	=	 PRL.CompanyPrefix
AND 	tMaxPurch_Rcpt_Line.NO_	=	 PRL.[Document No_] 
AND 	tMaxPurch_Rcpt_Line.maxbigint_timestamp	=	 PRL.bigint_timestamp 
AND 	tMaxPurch_Rcpt_Line.[Line No_]	=	 PRL.[Line No_] 
GROUP BY
PRL.CompanyPrefix, PRL.[Order No_], PRL.[Order Line No_]
		),

tSUMPurch_CrMemo_Line as (Select PCML.CompanyPrefix, PCML.[Order No_], PCML.[Order Line No_], MAX(PCML.[$systemCreatedAt]) AS MaxCrMemoDate ,SUM(PCML.Quantity) AS SumCrMemoQuantity from [dbo].[stg$Purch_ Cr_ Memo Line] PCML 
	left join  tMaxPurch_Rcpt_Line 
on 	tMaxPurch_Rcpt_Line.CompanyPrefix	=	 PCML.CompanyPrefix
AND 	tMaxPurch_Rcpt_Line.NO_	=	 PCML.[Document No_] 
AND 	tMaxPurch_Rcpt_Line.maxbigint_timestamp	=	 PCML.bigint_timestamp 
AND 	tMaxPurch_Rcpt_Line.[Line No_]	=	 PCML.[Line No_] 
GROUP BY
PCML.CompanyPrefix, PCML.[Order No_], PCML.[Order Line No_]
		),

tDocumentAndLineNumber
AS
(SELECT
		CAST([No_] AS VARCHAR(20)) AS [No_]
	   ,[Line No_]
	   ,[CompanyPrefix]
	   	FROM [tMaxPurchaseLine]
	UNION
	SELECT
		CAST([No_] AS VARCHAR(20)) AS [No_]
	   ,[Line No_]
	   ,[CompanyPrefix]
	FROM [tMaxPurchaseLineArchive]

),

tDistinctDocumentLineNumbers
AS
(SELECT
		[No_]
	   ,[Line No_]
	   ,[CompanyPrefix]
	FROM tDocumentAndLineNumber
	WHERE [No_] <> ''
	GROUP BY [NO_]
			,[Line No_]
			,[CompanyPrefix]
			),

/*
Get the list of companies used
*/
tCompanies
AS
(SELECT
		dCOM.[SK_Company] AS [Company]
	   ,dCOM.[CompanyPrefix]
	FROM [DW].[dCompany] dCOM
	WHERE dCOM.[SK_Company] <> -1),
/*
Set up dimensions and support tables used with the main data process
*/
tLocation
AS
(SELECT
		dLOC.[SK_Location]
	   ,tCOM.[CompanyPrefix]
	   ,dLOC.[LocationCode]
	FROM DW.[dLocation] dLOC
	LEFT OUTER JOIN [tCompanies] tCOM
		ON dLOC.[Company] = tCOM.[Company]),

tItem
AS
(SELECT
		dITM.[SK_Item]
	   ,tCOM.[CompanyPrefix]
	   ,dITM.[No]
	   ,dITM.ValidFrom
	   ,dITM.ValidTo
	FROM [DW].[dItem] dITM
	LEFT OUTER JOIN tCompanies tCOM
		ON dITM.[Company] = tCOM.[Company]),
tStore
AS
(SELECT
		dSTO.[SK_Store]
	   ,dSTO.[StoreNo]
	   ,tCOM.[CompanyPrefix]
	FROM [DW].[dStore] dSTO
	LEFT OUTER JOIN [tCompanies] tCOM
		ON dSTO.[Company] = tCOM.[Company]),
tVendor
AS
(SELECT
		dVEN.SK_Vendor
	   ,dVEN.VendorNo
	   ,tCOM.CompanyPrefix
	FROM DW.dVendor dVEN
	LEFT OUTER JOIN [tCompanies] tCOM
		ON dVEN.[Company] = tCOM.[Company]),

tReasons
AS
(SELECT
		dRea.[SK_ReturnReason]
	   ,dRea.[Code]
	   ,tCOM.CompanyPrefix
	FROM [DW].[dReturnReason] dRea
	LEFT OUTER JOIN [tCompanies] tCOM
		ON dRea.[Company] = tCOM.[Company]),

tPurchases
AS
(SELECT  DISTINCT
		DistNo.[CompanyPrefix]
	   ,COALESCE(PH.[Assigned User ID], PHA.[Assigned User ID]) AS [Assigned User ID]
	   ,COALESCE(PH.[Buy-from Vendor Name], PHA.[Buy-from Vendor Name]) AS [Buy-from Vendor Name]
	   ,COALESCE(PH.[Buy-from Vendor No_], PHA.[Buy-from Vendor No_]) AS [Buy-from Vendor No_]
	   ,COALESCE(PH.[Document Date], PHA.[Document Date]) AS [Document Date]
	   ,cast([DistNo].[No_] AS varchar(20)) AS [Order Number]
	   ,COALESCE(PH.[Due Date], PHA.[Due Date]) AS [Invoice Due Date]
	   ,COALESCE(sPL.[Expected Receipt Date], PLA.[Expected Receipt Date]) AS [Expected Receipt Date]
	   ,COALESCE(sPL.[Line No_], PLA.[Line No_]) AS [Order Line No]
	   ,COALESCE(sPL.[Location Code], PLA.[Location Code]) AS [Location Code]
	   ,COALESCE(sPL.[No_], PLA.[No_]) AS [Item Number]
	   ,COALESCE(PH.[Order Date], PHA.[Order Date]) AS [Order Date]
	   ,COALESCE(sPL.[Planned Receipt Date], PLA.[Planned Receipt Date]) AS [Planned Receipt Date]
	   ,cast(COALESCE(sPL.[$systemCreatedAt], PLA.[$systemCreatedAt]) as date) AS [Purchase Create Date]
		,COALESCE(PH.[Posting Date], PHA.[Posting Date]) AS [Posting Date]
	   ,COALESCE(sPL.[Promised Receipt Date], PLA.[Promised Receipt Date]) AS [Promised Receipt Date]
	   ,sPL.[Qty_ per Unit of Measure] AS [Qty per Unit of Measure]
	   ,COALESCE(sPL.[Quantity], PLA.[Quantity]) AS [Quantity]
	   ,COALESCE(sPL.[Quantity (Base)] , PLA.[Quantity (Base)]) AS [Quantity (Base)]
	   ,PRL.SumRcptQuantity
	   ,PRL.MaxRcptDate
	   ,PCML.MaxCrMemoDate
	   ,PCML.SumCrMemoQuantity
	   ,COALESCE(PH.[Requested Receipt Date], PHA.[Requested Receipt Date]) AS [Requested Receipt Date]
	   
	   ,COALESCE(PH.[Status], PHA.[Status]) AS [Status]
	   ,COALESCE(sPL.[Unit of Measure], PLA.[Unit of Measure]) AS [Unit of Measure]
	   ,COALESCE(sPL.[Unit Cost (LCY)], PLA.[Unit Cost (LCY)]) AS [Unit Cost (LCY)]
	FROM tDistinctDocumentLineNumbers DistNo
	LEFT OUTER JOIN tPurchaseLine sPL
		ON [DistNo].[No_] = sPL.[Document No_]
		AND DistNo.[CompanyPrefix] = sPL.[CompanyPrefix]
		AND DistNo.[Line No_] = sPL.[Line No_]
	LEFT OUTER JOIN tPurchaseHeader PH
		ON [DistNo].[No_] = PH.[No_]
		AND DistNo.[CompanyPrefix] = PH.[CompanyPrefix]

	left OUTER JOIN tSUMPurch_Rcpt_Line PRL
		ON [DistNo].[No_] = PRL.[Order No_]
		AND DistNo.[CompanyPrefix] = PRL.[CompanyPrefix]
		AND DistNo.[Line No_] = PRL.[Order Line No_]

	left OUTER JOIN tSUMPurch_CrMemo_Line PCML
		ON [DistNo].[No_] = PCML.[Order No_]
		AND DistNo.[CompanyPrefix] = PCML.[CompanyPrefix]
		AND DistNo.[Line No_] = PCML.[Order Line No_]

	LEFT OUTER JOIN tPurchaseLineArchive PLA
		ON [DistNo].[No_] = PLA.[Document No_]
		AND DistNo.[CompanyPrefix] = PLA.[CompanyPrefix]
		AND DistNo.[Line No_] = PLA.[Line No_]
	LEFT OUTER JOIN tPurchaseHeaderArchive PHA
		ON [DistNo].[No_] = PHA.[No_]
		AND DistNo.[CompanyPrefix] = PHA.[CompanyPrefix]

	)
	,
factPurchases
AS
(
SELECT
		tPL.[CompanyPrefix]
	   ,cast(tPL.[Order Number] as varchar(50)) + '-' + cast(tPL.[Order Line No] as varchar(10))  + '-' + cast(tPL.[CompanyPrefix] as varchar(50)) AS OrderNumberAndLineKey
	   ,COALESCE(tVEN.[SK_Vendor], -1) AS [SK_Vendor]
	   ,COALESCE(tITM.[SK_Item], -1) AS [SK_Item]
	   ,COALESCE(tLOC.[SK_Location], -1) AS [SK_Location]
	   ,IIF(tPL.[Document Date] = '1753-01-01', NULL, tPL.[Document Date]) AS [Document Date]
	   ,IIF(tPL.[Order Date] = '1753-01-01', NULL, tPL.[Order Date]) AS [Order Date]
	   ,IIF(tPL.[Invoice Due Date] = '1753-01-01', NULL, tPL.[Invoice Due Date]) AS [Invoice Due Date]
	   ,IIF(tPL.[Expected Receipt Date] = '1753-01-01', NULL, tPL.[Expected Receipt Date]) AS [Expected Receipt Date]
	   ,IIF(tPL.[Planned Receipt Date] = '1753-01-01', NULL, tPL.[Planned Receipt Date]) AS [Planned Receipt Date]
	   ,IIF(tPL.[Purchase Create Date] = '1753-01-01', NULL, tPL.[Purchase Create Date]) AS [Purchase Create Date]
	   ,IIF(tPL.[Posting Date] = '1753-01-01', NULL, tPL.[Posting Date]) AS [Posting Date]
	   ,IIF(tPL.[Promised Receipt Date] = '1753-01-01', NULL, tPL.[Promised Receipt Date]) AS [Promised Receipt Date]
	   ,IIF(tPL.[Requested Receipt Date] = '1753-01-01', NULL, tPL.[Requested Receipt Date]) AS [Requested Receipt Date]
	   ,tPL.[Assigned User ID] AS [Assigned User ID]
	   ,tPL.[Order Number] AS [Order Number]
	   ,tPL.[Order Line No] AS [Order Line No]
	   ,tPL.[Qty per Unit of Measure] AS [Qty per Unit of Measure]
	   ,tPL.[Quantity] AS [Quantity_]
	   ,tPL.[Quantity (Base)] AS [Quantity (Base)_]
	   ,CASE
  WHEN COALESCE(tPL.[Quantity], 0) - COALESCE(tPL.SumRcptQuantity, 0) + COALESCE(tPL.SumCrMemoQuantity, 0) = 0 THEN 'Closed'
  WHEN COALESCE(tPL.[Quantity], 0) - COALESCE(tPL.SumRcptQuantity, 0) + COALESCE(tPL.SumCrMemoQuantity, 0) > 0 AND tPL.[Status] = 1 THEN 'Open, partial deliveried'
  WHEN tPL.[Status] = 0 THEN 'Open'
  ELSE 'Unknown/Deleted'
  END AS [Status]

	   ,tPL.[Unit of Measure] AS [Unit of Measure]
	   ,tPL.[Unit Cost (LCY)] AS [Unit Cost (LCY)]
	   ,GETDATE() AS 'Batchdate'

	   ,CASE
  WHEN datediff(day,
  COALESCE(
  IIF(tPL.[Requested Receipt Date] = '1753-01-01', NULL, tPL.[Requested Receipt Date])
  ,IIF(tPL.[Promised Receipt Date] = '1753-01-01', NULL, tPL.[Promised Receipt Date])
  )
  , tpl.MaxRcptDate) > 0
  AND COALESCE(tPL.[Quantity], 0) - COALESCE(tPL.SumRcptQuantity, 0) + COALESCE(tPL.SumCrMemoQuantity, 0) = 0
  THEN 'Backlog'
  WHEN datediff(day,
  COALESCE(
  IIF(tPL.[Requested Receipt Date] = '1753-01-01', NULL, tPL.[Requested Receipt Date])
  ,IIF(tPL.[Promised Receipt Date] = '1753-01-01', NULL, tPL.[Promised Receipt Date])
  )
  , tpl.MaxRcptDate) > 0
  AND COALESCE(tPL.[Quantity], 0) - COALESCE(tPL.SumRcptQuantity, 0) + COALESCE(tPL.SumCrMemoQuantity, 0) > 0 AND tPL.[Status] = 1
  THEN 'Backlog'
  WHEN datediff(day,
  COALESCE(
  IIF(tPL.[Requested Receipt Date] = '1753-01-01', NULL, tPL.[Requested Receipt Date])
  ,IIF(tPL.[Promised Receipt Date] = '1753-01-01', NULL, tPL.[Promised Receipt Date])
  )
  , GETDATE()) > 0
  AND tPL.[Status] = 0
  THEN 'Backlog'
  ELSE 'Not Backlog'
  END AS [Order on Backlog]

  ,CASE
  WHEN datediff(day,
  COALESCE(
  IIF(tPL.[Requested Receipt Date] = '1753-01-01', NULL, tPL.[Requested Receipt Date])
  ,IIF(tPL.[Promised Receipt Date] = '1753-01-01', NULL, tPL.[Promised Receipt Date])
  )
  ,tpl.MaxRcptDate) < 0
  AND COALESCE(tPL.[Quantity], 0) - COALESCE(tPL.SumRcptQuantity, 0) + COALESCE(tPL.SumCrMemoQuantity, 0) = 0
  THEN 'On Time'
  WHEN datediff(day,
  COALESCE(
  IIF(tPL.[Requested Receipt Date] = '1753-01-01', NULL, tPL.[Requested Receipt Date])
  ,IIF(tPL.[Promised Receipt Date] = '1753-01-01', NULL, tPL.[Promised Receipt Date])
  )
  , tpl.MaxRcptDate) > 0
  AND COALESCE(tPL.[Quantity], 0) - COALESCE(tPL.SumRcptQuantity, 0) + COALESCE(tPL.SumCrMemoQuantity, 0) = 0
  THEN 'Not On Time'
  WHEN COALESCE(tPL.[Quantity], 0) - COALESCE(tPL.SumRcptQuantity, 0) + COALESCE(tPL.SumCrMemoQuantity, 0) > 0 AND tPL.[Status] = 1 THEN 'Open, partial deliveried'
  WHEN tPL.[Status] = 0 THEN 'Open'
  ELSE 'Unknown/Deleted'
  END AS [On Time]
	FROM [tPurchases] tPL
	

	LEFT OUTER JOIN [tItem] tITM
		ON tPL.[CompanyPrefix] = tITM.[CompanyPrefix]
		AND tPL.[Item Number] = tITM.[No]
		AND tPL.[Posting Date] >= tITM.ValidFrom
		AND tPL.[Posting Date] <= tITM.ValidTo -- GETTING THE CORRECT SURROGATE KEY VERSION OF THE DIMENSION (SCD Type2)	
	LEFT OUTER JOIN [tLocation] tLOC
		ON tPL.[CompanyPrefix] = tLOC.[CompanyPrefix]
		AND tPL.[Location Code] = tLOC.[LocationCode]
	LEFT OUTER JOIN tVendor tVen
		ON tPL.CompanyPrefix = tVen.CompanyPrefix
		AND tPL.[Buy-from Vendor No_] = tVen.VendorNo
)

MERGE [DW].[fPurchaseOrders] AS TARGET USING (SELECT
		tPL.[CompanyPrefix]
	   ,cast(tPL.[Order Number] as varchar(50)) + '-' + cast(tPL.[Order Line No] as varchar(10))  + '-' + cast(tPL.[CompanyPrefix] as varchar(50)) AS OrderNumberAndLineKey
	   ,COALESCE(tVEN.[SK_Vendor], -1) AS [SK_Vendor]
	   ,COALESCE(tITM.[SK_Item], -1) AS [SK_Item]
	   ,COALESCE(tLOC.[SK_Location], -1) AS [SK_Location]
	   ,IIF(tPL.[Document Date] = '1753-01-01', NULL, tPL.[Document Date]) AS [Document Date]
	   ,IIF(tPL.[Order Date] = '1753-01-01', NULL, tPL.[Order Date]) AS [Order Date]
	   ,IIF(tPL.[Invoice Due Date] = '1753-01-01', NULL, tPL.[Invoice Due Date]) AS [Invoice Due Date]
	   ,IIF(tPL.[Expected Receipt Date] = '1753-01-01', NULL, tPL.[Expected Receipt Date]) AS [Expected Receipt Date]
	   ,IIF(tPL.[Planned Receipt Date] = '1753-01-01', NULL, tPL.[Planned Receipt Date]) AS [Planned Receipt Date]
	   ,IIF(tPL.[Purchase Create Date] = '1753-01-01', NULL, tPL.[Purchase Create Date]) AS [Purchase Create Date]
	   ,IIF(tPL.[Posting Date] = '1753-01-01', NULL, tPL.[Posting Date]) AS [Posting Date]
	   ,IIF(tPL.[Promised Receipt Date] = '1753-01-01', NULL, tPL.[Promised Receipt Date]) AS [Promised Receipt Date]
	   ,IIF(tPL.[Requested Receipt Date] = '1753-01-01', NULL, tPL.[Requested Receipt Date]) AS [Requested Receipt Date]
	   ,tPL.[Assigned User ID] AS [Assigned User ID]
	   ,tPL.[Order Number] AS [Order Number]
	   ,tPL.[Order Line No] AS [Order Line No]
	   ,tPL.[Qty per Unit of Measure] AS [Qty per Unit of Measure]
	   ,tPL.[Quantity] AS [Quantity_]
	   ,tPL.[Quantity (Base)] AS [Quantity (Base)_]
	   ,CASE
  WHEN COALESCE(tPL.[Quantity], 0) - COALESCE(tPL.SumRcptQuantity, 0) + COALESCE(tPL.SumCrMemoQuantity, 0) = 0 THEN 'Closed'
  WHEN COALESCE(tPL.[Quantity], 0) - COALESCE(tPL.SumRcptQuantity, 0) + COALESCE(tPL.SumCrMemoQuantity, 0) > 0 AND tPL.[Status] = 1 THEN 'Open, partial deliveried'
  WHEN tPL.[Status] = 0 THEN 'Open'
  ELSE 'Unknown/Deleted'
  END AS [Status]

	   ,tPL.[Unit of Measure] AS [Unit of Measure]
	   ,tPL.[Unit Cost (LCY)] AS [Unit Cost (LCY)]
	   ,GETDATE() AS 'Batchdate'

	   ,CASE
  WHEN datediff(day,
  COALESCE(
  IIF(tPL.[Requested Receipt Date] = '1753-01-01', NULL, tPL.[Requested Receipt Date])
  ,IIF(tPL.[Promised Receipt Date] = '1753-01-01', NULL, tPL.[Promised Receipt Date])
  )
  , tpl.MaxRcptDate) > 0
  AND COALESCE(tPL.[Quantity], 0) - COALESCE(tPL.SumRcptQuantity, 0) + COALESCE(tPL.SumCrMemoQuantity, 0) = 0
  THEN 'Backlog'
  WHEN datediff(day,
  COALESCE(
  IIF(tPL.[Requested Receipt Date] = '1753-01-01', NULL, tPL.[Requested Receipt Date])
  ,IIF(tPL.[Promised Receipt Date] = '1753-01-01', NULL, tPL.[Promised Receipt Date])
  )
  , tpl.MaxRcptDate) > 0
  AND COALESCE(tPL.[Quantity], 0) - COALESCE(tPL.SumRcptQuantity, 0) + COALESCE(tPL.SumCrMemoQuantity, 0) > 0 AND tPL.[Status] = 1
  THEN 'Backlog'
  WHEN datediff(day,
  COALESCE(
  IIF(tPL.[Requested Receipt Date] = '1753-01-01', NULL, tPL.[Requested Receipt Date])
  ,IIF(tPL.[Promised Receipt Date] = '1753-01-01', NULL, tPL.[Promised Receipt Date])
  )
  , GETDATE()) > 0
  AND tPL.[Status] = 0
  THEN 'Backlog'
  ELSE 'Not Backlog'
  END AS [Order on Backlog]

  ,CASE
  WHEN datediff(day,
  COALESCE(
  IIF(tPL.[Requested Receipt Date] = '1753-01-01', NULL, tPL.[Requested Receipt Date])
  ,IIF(tPL.[Promised Receipt Date] = '1753-01-01', NULL, tPL.[Promised Receipt Date])
  )
  ,tpl.MaxRcptDate) < 0
  AND COALESCE(tPL.[Quantity], 0) - COALESCE(tPL.SumRcptQuantity, 0) + COALESCE(tPL.SumCrMemoQuantity, 0) = 0
  THEN 'On Time'
  WHEN datediff(day,
  COALESCE(
  IIF(tPL.[Requested Receipt Date] = '1753-01-01', NULL, tPL.[Requested Receipt Date])
  ,IIF(tPL.[Promised Receipt Date] = '1753-01-01', NULL, tPL.[Promised Receipt Date])
  )
  , tpl.MaxRcptDate) > 0
  AND COALESCE(tPL.[Quantity], 0) - COALESCE(tPL.SumRcptQuantity, 0) + COALESCE(tPL.SumCrMemoQuantity, 0) = 0
  THEN 'Not On Time'
  WHEN COALESCE(tPL.[Quantity], 0) - COALESCE(tPL.SumRcptQuantity, 0) + COALESCE(tPL.SumCrMemoQuantity, 0) > 0 AND tPL.[Status] = 1 THEN 'Open, partial deliveried'
  WHEN tPL.[Status] = 0 THEN 'Open'
  ELSE 'Unknown/Deleted'
  END AS [On Time]
	FROM [tPurchases] tPL
	

	LEFT OUTER JOIN [tItem] tITM
		ON tPL.[CompanyPrefix] = tITM.[CompanyPrefix]
		AND tPL.[Item Number] = tITM.[No]
		AND tPL.[Posting Date] >= tITM.ValidFrom
		AND tPL.[Posting Date] <= tITM.ValidTo -- GETTING THE CORRECT SURROGATE KEY VERSION OF THE DIMENSION (SCD Type2)	
	LEFT OUTER JOIN [tLocation] tLOC
		ON tPL.[CompanyPrefix] = tLOC.[CompanyPrefix]
		AND tPL.[Location Code] = tLOC.[LocationCode]
	LEFT OUTER JOIN tVendor tVen
		ON tPL.CompanyPrefix = tVen.CompanyPrefix
		AND tPL.[Buy-from Vendor No_] = tVen.VendorNo) AS Source
ON Target.OrderNumberAndLineKey = Source.OrderNumberAndLineKey
	
WHEN MATCHED
	THEN UPDATE
		SET [CompanyPrefix] =  Source.[CompanyPrefix]
,[OrderNumberAndLineKey] =  Source.[OrderNumberAndLineKey]
,[SK_Vendor] =  Source.[SK_Vendor]
,[SK_Item] =  Source.[SK_Item]
,[SK_Location] =  Source.[SK_Location]
,[Document Date] =  Source.[Document Date]
,[Order Date] =  Source.[Order Date]
,[Invoice Due Date] =  Source.[Invoice Due Date]
,[Expected Receipt Date] =  Source.[Expected Receipt Date]
,[Planned Receipt Date] =  Source.[Planned Receipt Date]
,[Purchase Create Date] =  Source.[Purchase Create Date]
,[Posting Date] =  Source.[Posting Date]
,[Promised Receipt Date] =  Source.[Promised Receipt Date]
,[Requested Receipt Date] =  Source.[Requested Receipt Date]
,[Assigned User ID] =  Source.[Assigned User ID]
,[Order Number] =  Source.[Order Number]
,[Order Line No] =  Source.[Order Line No]
,[Qty per Unit of Measure] =  Source.[Qty per Unit of Measure]
,[Quantity_] =  Source.[Quantity_]
,[Quantity (Base)_] =  Source.[Quantity (Base)_]
,[Status] =  Source.[Status]
,[Unit of Measure] =  Source.[Unit of Measure]
,[Unit Cost (LCY)] =  Source.[Unit Cost (LCY)]
,[Batchdate] =  Source.[Batchdate]
,[Order on Backlog] =  Source.[Order on Backlog]
,[On Time] =  Source.[On Time]




WHEN NOT MATCHED BY TARGET
	THEN INSERT ([CompanyPrefix]
      ,[OrderNumberAndLineKey]
      ,[SK_Vendor]
      ,[SK_Item]
      ,[SK_Location]
      ,[Document Date]
      ,[Order Date]
      ,[Invoice Due Date]
      ,[Expected Receipt Date]
      ,[Planned Receipt Date]
      ,[Purchase Create Date]
      ,[Posting Date]
      ,[Promised Receipt Date]
      ,[Requested Receipt Date]
      ,[Assigned User ID]
      ,[Order Number]
      ,[Order Line No]
      ,[Qty per Unit of Measure]
      ,[Quantity_]
      ,[Quantity (Base)_]
      ,[Status]
      ,[Unit of Measure]
      ,[Unit Cost (LCY)]
      ,[Batchdate]
      ,[Order on Backlog]
      ,[On Time])
			VALUES (Source.[CompanyPrefix]
,Source.[OrderNumberAndLineKey]
,Source.[SK_Vendor]
,Source.[SK_Item]
,Source.[SK_Location]
,Source.[Document Date]
,Source.[Order Date]
,Source.[Invoice Due Date]
,Source.[Expected Receipt Date]
,Source.[Planned Receipt Date]
,Source.[Purchase Create Date]
,Source.[Posting Date]
,Source.[Promised Receipt Date]
,Source.[Requested Receipt Date]
,Source.[Assigned User ID]
,Source.[Order Number]
,Source.[Order Line No]
,Source.[Qty per Unit of Measure]
,Source.[Quantity_]
,Source.[Quantity (Base)_]
,Source.[Status]
,Source.[Unit of Measure]
,Source.[Unit Cost (LCY)]
,Source.[Batchdate]
,Source.[Order on Backlog]
,Source.[On Time]

)
;
ELSE
SELECT 'RowCount' = 0
END

SELECT
	'RowCount' = @@rowcount
GO


/****** Object:  StoredProcedure [dbo].[factPurchaseReceipts]    Script Date: 28/04/2022 09:54:47 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[factPurchaseReceipts]') AND type in (N'P'))
DROP PROCEDURE [dbo].[factPurchaseReceipts]
GO

/****** Object:  StoredProcedure [dbo].[factPurchaseReceipts]    Script Date: 28/04/2022 09:54:47 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		Kristinn Ţór Sigurjónsson
-- Create date: 2022-04-27
-- Description:	SP for factPurchaseReceipts
-- =============================================
CREATE PROCEDURE [dbo].[factPurchaseReceipts] 

	@CurrentRowID bigint,
	@NewRowID bigint
	
AS
BEGIN
SET NOCOUNT ON;

--CHECK if All needed Purchase staging tables exist
IF (Select count(*) from sys.tables where SCHEMA_NAME(schema_id) LIKE 'dbo' 
AND name IN ('stg$Purch_ Cr_ Memo Hdr_'
,'stg$Purch_ Cr_ Memo Line'
,'stg$Purch_ Inv_ Header'
,'stg$Purch_ Inv_ Line'
,'stg$Purch_ Rcpt_ Header'
,'stg$Purch_ Rcpt_ Line'
,'stg$Purchase Header'
,'stg$Purchase Header Archive'
,'stg$Purchase Line'
,'stg$Purchase Line Archive'))
= 10
AND COL_LENGTH('[dbo].[stg$Purchase Line]', '$systemCreatedAt') IS NOT NULL

WITH

	 /*
   Get Latest version of Headers
   */
	 tMaxPurchaseHeader
	 AS (SELECT [CompanyPrefix],
				CAST( [No_] AS VARCHAR(20) ) AS [No_],
				MAX( bigint_timestamp ) AS MaxTimestamp
		   FROM [dbo].[stg$Purchase Header]
		   GROUP BY [CompanyPrefix],
					[No_]),

	 tMaxPurchaseHeaderArchive
	 AS (SELECT [CompanyPrefix],
				CAST( [No_] AS VARCHAR(20) ) AS [No_],
				MAX( bigint_timestamp ) AS MaxTimestamp
		   FROM [dbo].[stg$Purchase Header Archive]
		   GROUP BY [CompanyPrefix],
					[No_]),

	 tMaxPurchaseLine
	 AS (SELECT CAST( [Document No_] AS VARCHAR(20) ) AS [NO_],
				[Line No_],
				[CompanyPrefix],
				MAX( bigint_timestamp ) AS maxbigint_timestamp
		   FROM [stg$Purchase Line]
		   GROUP BY [Document No_],
					[Line No_],
					[CompanyPrefix]),

	 tMaxPurchaseLineArchive
	 AS (SELECT CAST( [Document No_] AS VARCHAR(20) ) AS [NO_],
				[Line No_],
				[CompanyPrefix],
				MAX( bigint_timestamp ) AS maxbigint_timestamp
		   FROM [stg$Purchase Line Archive]
		   GROUP BY [Document No_],
					[Line No_],
					[CompanyPrefix]),

tMaxPurchaseRcptHeader
	AS
	(SELECT
		[CompanyPrefix]
	   ,CAST([Order No_] AS VARCHAR(20)) AS [Order No_]
	   ,MAX(bigint_timestamp) AS MaxTimestamp
	FROM [dbo].[stg$Purch_ Rcpt_ Header]
	GROUP BY [CompanyPrefix]
			,[Order No_]
			),

tMaxPurch_Rcpt_Line AS (
	SELECT
		CAST([Order No_] AS VARCHAR(20)) AS [NO_]
	   ,[Line No_]
	   ,[CompanyPrefix]
	   ,MAX(bigint_timestamp) AS maxbigint_timestamp
	FROM [stg$Purch_ Rcpt_ Line]
	GROUP BY [Order No_]
			,[Line No_]
			,[CompanyPrefix]),

	 tPurchaseHeader
	 AS (SELECT H.No_,
				H.[CompanyPrefix],
				H.[Posting Date],
				H.[Order Date],
				H.[Buy-from Vendor No_],
				H.[Status],
				H.[Expected Receipt Date],
				H.[Promised Receipt Date],
				H.[Requested Receipt Date]
		   FROM [stg$Purchase Header] H
		   LEFT JOIN
		   tMaxPurchaseHeader
		   ON tMaxPurchaseHeader.CompanyPrefix=H.CompanyPrefix
		   AND tMaxPurchaseHeader.No_=H.No_
		   AND MaxTimestamp=H.bigint_timestamp
		   GROUP BY H.No_,
					H.[CompanyPrefix],
					H.[Posting Date],
					H.[Order Date],
					H.[Buy-from Vendor No_],
					H.[Status],
					H.[Expected Receipt Date],
					H.[Promised Receipt Date],
					H.[Requested Receipt Date]),

	 tPurchaseHeaderArchive
	 AS (SELECT H.No_,
				H.[CompanyPrefix],
				H.[Posting Date],
				H.[Order Date],
				H.[Buy-from Vendor No_],
				H.[Status],
				H.[Expected Receipt Date],
				H.[Promised Receipt Date],
				H.[Requested Receipt Date]
		   FROM [stg$Purchase Header Archive] H
		   LEFT JOIN
		   tMaxPurchaseHeaderArchive MaxHA
		   ON MaxHA.CompanyPrefix=H.CompanyPrefix
		   AND MaxHA.No_=H.No_
		   AND MaxHA.MaxTimestamp=H.bigint_timestamp
		   WHERE H.[Version No_]=1
		   GROUP BY H.No_,
					H.[CompanyPrefix],
					H.[Posting Date],
					H.[Order Date],
					H.[Buy-from Vendor No_],
					H.[Status],
					H.[Expected Receipt Date],
					H.[Promised Receipt Date],
					H.[Requested Receipt Date]),

	 tPurchaseReceiptHeader
	 AS (SELECT PRH.[CompanyPrefix],
				PRH.[Order No_],
				PRH.[Order Date],
				PRH.[Posting Date],
				PRH.[Buy-from Vendor No_]
		   FROM [stg$Purch_ Rcpt_ Header] PRH
		   LEFT JOIN
		   tMaxPurchaseHeader
		   ON tMaxPurchaseHeader.CompanyPrefix=PRH.CompanyPrefix
		   AND tMaxPurchaseHeader.No_=PRH.[Order No_]
		   AND MaxTimestamp=PRH.bigint_timestamp
		   GROUP BY PRH.[CompanyPrefix],
					PRH.[Order No_],
					PRH.[Order Date],
					PRH.[Posting Date],
					PRH.[Buy-from Vendor No_]),

		 tPurch_ReceiptLine
	 AS (SELECT PRL.*
		   FROM [stg$Purch_ Rcpt_ Line] PRL
		   LEFT JOIN
		   tMaxPurch_Rcpt_Line
		   ON tMaxPurch_Rcpt_Line.CompanyPrefix=PRL.CompanyPrefix
		   AND tMaxPurch_Rcpt_Line.No_=PRL.[Document No_]
		   AND tMaxPurch_Rcpt_Line.maxbigint_timestamp=PRL.bigint_timestamp
		   AND tMaxPurch_Rcpt_Line.[Line No_]=PRL.[Line No_]),

	 tDistinctDocumentLineNumbers
	 AS (SELECT CAST( [No_] AS VARCHAR(20) ) AS [No_],
				[Line No_],
				[CompanyPrefix]
		   FROM tMaxPurch_Rcpt_Line
		   WHERE [No_]<>''
		   GROUP BY [No_],
					[Line No_],
					[CompanyPrefix]),

	 tPurchaseLine
	 AS (SELECT sPL.[CompanyPrefix],
				sPL.[Document No_],
				sPL.[Line No_],
				sPL.[Promised Receipt Date],
				sPL.[Planned Receipt Date],
				sPL.[Expected Receipt Date],
				sPL.[Requested Receipt Date]
		   FROM [stg$Purchase Line] sPL
		   LEFT JOIN
		   tMaxPurchaseLine
		   ON tMaxPurchaseLine.CompanyPrefix=sPL.CompanyPrefix
		   AND tMaxPurchaseLine.No_=sPL.[Document No_]
		   AND maxbigint_timestamp=sPL.bigint_timestamp
		   AND tMaxPurchaseLine.[Line No_]=sPL.[Line No_]
		   GROUP BY sPL.[CompanyPrefix],
					sPL.[Document No_],
					sPL.[Line No_],
					sPL.[Promised Receipt Date],
					sPL.[Planned Receipt Date],
					sPL.[Expected Receipt Date],
					sPL.[Requested Receipt Date]),

	 tPurchaseLineArchive
	 AS (SELECT PLA.[CompanyPrefix],
				PLA.[Document No_],
				PLA.[Line No_],
				PLA.[Promised Receipt Date],
				PLA.[Planned Receipt Date],
				PLA.[Expected Receipt Date],
				PLA.[Requested Receipt Date]
		   FROM [stg$Purchase Line Archive] PLA
		   LEFT JOIN
		   tMaxPurchaseLineArchive
		   ON tMaxPurchaseLineArchive.CompanyPrefix=PLA.CompanyPrefix
		   AND tMaxPurchaseLineArchive.No_=PLA.[Document No_]
		   AND tMaxPurchaseLineArchive.maxbigint_timestamp=PLA.bigint_timestamp
		   AND tMaxPurchaseLineArchive.[Line No_]=PLA.[Line No_]
		   WHERE PLA.[Version No_]=1
		   GROUP BY PLA.[CompanyPrefix],
					PLA.[Document No_],
					PLA.[Line No_],
					PLA.[Promised Receipt Date],
					PLA.[Planned Receipt Date],
					PLA.[Expected Receipt Date],
				PLA.[Requested Receipt Date]),



	 /*
   Get the list of companies used
   */
	 tCompanies
	 AS (SELECT dCOM.[SK_Company] AS [Company],
				dCOM.[CompanyPrefix]
		   FROM [DW].[dCompany] dCOM
		   WHERE dCOM.[SK_Company]<>-1),
	 /*
   Set up dimensions and support tables used with the main data process
   */
	 tLocation
	 AS (SELECT dLOC.[SK_Location],
				tCOM.[CompanyPrefix],
				dLOC.[LocationCode]
		   FROM DW.[dLocation] dLOC
		   LEFT OUTER JOIN
		   [tCompanies] tCOM
		   ON dLOC.[Company]=tCOM.[Company]),

	 tItem
	 AS (SELECT dITM.[SK_Item],
				tCOM.[CompanyPrefix],
				dITM.[No],
				dITM.ValidFrom,
				dITM.ValidTo
		   FROM [DW].[dItem] dITM
		   LEFT OUTER JOIN
		   tCompanies tCOM
		   ON dITM.[Company]=tCOM.[Company]),
	 tStore
	 AS (SELECT dSTO.[SK_Store],
				dSTO.[StoreNo],
				tCOM.[CompanyPrefix]
		   FROM [DW].[dStore] dSTO
		   LEFT OUTER JOIN
		   [tCompanies] tCOM
		   ON dSTO.[Company]=tCOM.[Company]),
	 tVendor
	 AS (SELECT dVEN.SK_Vendor,
				dVEN.VendorNo,
				tCOM.CompanyPrefix
		   FROM DW.dVendor dVEN
		   LEFT OUTER JOIN
		   [tCompanies] tCOM
		   ON dVEN.[Company]=tCOM.[Company]),

	 tReasons
	 AS (SELECT dRea.[SK_ReturnReason],
				dRea.[Code],
				tCOM.CompanyPrefix
		   FROM [DW].[dReturnReason] dRea
		   LEFT OUTER JOIN
		   [tCompanies] tCOM
		   ON dRea.[Company]=tCOM.[Company]),

	 tPurchases
	 AS (SELECT DistNo.[CompanyPrefix],
				PRH.[Buy-from Vendor No_] AS [Buy-from Vendor No_],
				CAST( [DistNo].[No_] AS VARCHAR(20) ) AS [Order Number],
				PRL.[Line No_] AS [Order Line No],
				PRL.[Location Code] AS [Location Code],
				PRL.[No_] AS [Item Number],
				PRL.[Posting Date] AS [Receipt Posting Date],
				PRH.[Order Date],
				COALESCE(sPL.[Expected Receipt Date], PRL.[Expected Receipt Date]) AS [Expected Receipt Date],
				COALESCE(sPL.[Planned Receipt Date], PRL.[Planned Receipt Date]) AS [Planned Receipt Date],
				COALESCE(sPL.[Promised Receipt Date], PRL.[Promised Receipt Date]) AS [Promised Receipt Date],
				CAST( PRL.[$systemCreatedAt] AS DATETIME ) AS [Purchase Receipt Create Date],
				COALESCE( PH.[Posting Date], PHA.[Posting Date], PRH.[Posting Date] ) AS [Posting Date],
				COALESCE( sPL.[Requested Receipt Date], PLA.[Requested Receipt Date] ) AS [Requested Receipt Date],
				PRL.[Quantity] AS [Quantity Receipt],
				COALESCE( PH.[Status], PHA.[Status] ) AS [Status]
		   FROM tDistinctDocumentLineNumbers DistNo
		   LEFT JOIN
		   tPurchaseLine sPL
		   ON [DistNo].[No_]=sPL.[Document No_]
		   AND DistNo.[CompanyPrefix]=sPL.[CompanyPrefix]
		   AND DistNo.[Line No_]=sPL.[Line No_]
		   LEFT JOIN
		   tPurchaseHeader PH
		   ON [DistNo].[No_]=PH.[No_]
		   AND DistNo.[CompanyPrefix]=PH.[CompanyPrefix]
		   LEFT OUTER JOIN
		   tPurchaseReceiptHeader PRH
		   ON [DistNo].[No_]=PRH.[Order No_]
		   AND DistNo.[CompanyPrefix]=PRH.[CompanyPrefix]
		   LEFT OUTER JOIN
		   tPurch_ReceiptLine PRL
		   ON [DistNo].[No_]=PRL.[Order No_]
		   AND DistNo.[CompanyPrefix]=PRL.[CompanyPrefix]
		   AND DistNo.[Line No_]=PRL.[Line No_]
		   LEFT JOIN
		   tPurchaseLineArchive PLA
		   ON [DistNo].[No_]=PLA.[Document No_]
		   AND DistNo.[CompanyPrefix]=PLA.[CompanyPrefix]
		   AND DistNo.[Line No_]=PLA.[Line No_]
		   LEFT JOIN
		   tPurchaseHeaderArchive PHA
		   ON [DistNo].[No_]=PHA.[No_]
		   AND DistNo.[CompanyPrefix]=PHA.[CompanyPrefix])
		   
		   

MERGE [DW].[fPurchaseReceipts] AS TARGET USING (SELECT tPL.[CompanyPrefix],
	 cast(tPL.[Order Number] as varchar(50)) + '-' + cast(tPL.[Order Line No] as varchar(10))  + '-' + cast(tPL.[CompanyPrefix] as varchar(50)) AS OrderNumberAndLineKey,
				COALESCE( tVen.[SK_Vendor],-1 ) AS [SK_Vendor],
				COALESCE( tITM.[SK_Item],-1 ) AS [SK_Item],
				COALESCE( tLOC.[SK_Location],-1 ) AS [SK_Location],			
				IIF( tPL.[Expected Receipt Date]='1753-01-01', NULL, tPL.[Expected Receipt Date] ) AS [Expected Receipt Date],
				IIF( tPL.[Planned Receipt Date]='1753-01-01', NULL, tPL.[Planned Receipt Date] ) AS [Planned Receipt Date],
				IIF( tPL.[Receipt Posting Date]='1753-01-01', NULL, tPL.[Receipt Posting Date] ) AS [Receipt Posting Date],
				IIF( tPL.[Purchase Receipt Create Date]='1753-01-01', NULL, tPL.[Purchase Receipt Create Date] ) AS [Purchase Receipt Create Date],
				IIF( tPL.[Promised Receipt Date]='1753-01-01', NULL, tPL.[Promised Receipt Date] ) AS [Promised Receipt Date],
				IIF( tPL.[Requested Receipt Date]='1753-01-01', NULL, tPL.[Requested Receipt Date] ) AS [Requested Receipt Date],
				tPL.[Order Number] AS [Order Number],
				tPL.[Order Line No] AS [Order Line No],
				tPL.[Quantity Receipt] AS [Quantity Receipt_],
				tPL.[Status] AS [Status],
				GETDATE( ) AS 'Batchdate'
		   FROM [tPurchases] tPL
		   LEFT OUTER JOIN
		   [tItem] tITM
		   ON tPL.[CompanyPrefix]=tITM.[CompanyPrefix]
		   AND tPL.[Item Number]=tITM.[No]
		   AND tPL.[Posting Date]>=tITM.ValidFrom
		   AND tPL.[Posting Date]<=tITM.ValidTo -- GETTING THE CORRECT SURROGATE KEY VERSION OF THE DIMENSION (SCD Type2)	
		   LEFT OUTER JOIN
		   [tLocation] tLOC
		   ON tPL.[CompanyPrefix]=tLOC.[CompanyPrefix]
		   AND tPL.[Location Code]=tLOC.[LocationCode]
		   LEFT OUTER JOIN
		   tVendor tVen
		   ON tPL.CompanyPrefix=tVen.CompanyPrefix
		   AND tPL.[Buy-from Vendor No_]=tVen.VendorNo) AS Source
ON Target.OrderNumberAndLineKey = Source.OrderNumberAndLineKey
	
WHEN MATCHED
	THEN UPDATE
		SET [CompanyPrefix] =  Source.[CompanyPrefix]
,[OrderNumberAndLineKey] =  Source.[OrderNumberAndLineKey]
,[SK_Vendor] =  Source.[SK_Vendor]
,[SK_Item] =  Source.[SK_Item]
,[SK_Location] =  Source.[SK_Location]
,[Expected Receipt Date] =  Source.[Expected Receipt Date]
,[Planned Receipt Date] =  Source.[Planned Receipt Date]
,[Receipt Posting Date] =  Source.[Receipt Posting Date]
,[Purchase Receipt Create Date] =  Source.[Purchase Receipt Create Date]
,[Promised Receipt Date] =  Source.[Promised Receipt Date]
,[Requested Receipt Date] =  Source.[Requested Receipt Date]
,[Order Number] =  Source.[Order Number]
,[Order Line No] =  Source.[Order Line No]
,[Quantity Receipt_] =  Source.[Quantity Receipt_]
,[Status] =  Source.[Status]
,[Batchdate] =  Source.[Batchdate]


WHEN NOT MATCHED BY TARGET
	THEN INSERT ([CompanyPrefix]
      ,[OrderNumberAndLineKey]
      ,[SK_Vendor]
      ,[SK_Item]
      ,[SK_Location]
      ,[Expected Receipt Date]
      ,[Planned Receipt Date]
      ,[Receipt Posting Date]
      ,[Purchase Receipt Create Date]
      ,[Promised Receipt Date]
      ,[Requested Receipt Date]
      ,[Order Number]
      ,[Order Line No]
      ,[Quantity Receipt_]
      ,[Status]
	  ,[Batchdate])
			VALUES ( Source.[CompanyPrefix]
, Source.[OrderNumberAndLineKey]
, Source.[SK_Vendor]
, Source.[SK_Item]
, Source.[SK_Location]
, Source.[Expected Receipt Date]
, Source.[Planned Receipt Date]
, Source.[Receipt Posting Date]
, Source.[Purchase Receipt Create Date]
, Source.[Promised Receipt Date]
, Source.[Requested Receipt Date]
, Source.[Order Number]
, Source.[Order Line No]
, Source.[Quantity Receipt_]
, Source.[Status]
, Source.[Batchdate]

)
;
ELSE
SELECT 'RowCount' = 0
END

SELECT
	'RowCount' = @@rowcount
GO
